import os
import sys
from datetime import datetime, date, timedelta
import random

# 添加项目根目录到Python路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

# 导入必要的模块
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash

# 数据库文件路径（相对路径）
DB_PATH = os.path.join('instance', 'inventory.db')

# 创建Flask应用实例
app = Flask(__name__, instance_relative_config=True)

# 配置数据库
app.config['SECRET_KEY'] = 'inventory_system_secret_key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///inventory.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 初始化数据库
db = SQLAlchemy(app)

# 定义数据模型（与models.py保持一致）
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    role = db.Column(db.String(20), nullable=False, default='operator')  # 角色：admin 或 operator
    name = db.Column(db.String(100), nullable=True)  # 用户姓名
    gender = db.Column(db.String(10))  # 性别：male 或 female
    birth_date = db.Column(db.Date)  # 出生日期
    phone = db.Column(db.String(20))  # 联系电话
    email = db.Column(db.String(100), unique=True)  # 邮箱
    address = db.Column(db.Text)  # 地址
    avatar = db.Column(db.String(255))  # 头像地址
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

class Supplier(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 供应商名称
    contact_person = db.Column(db.String(50))  # 联系人
    phone = db.Column(db.String(20))  # 联系电话
    email = db.Column(db.String(100))  # 邮箱
    address = db.Column(db.Text)  # 地址
    status = db.Column(db.String(20), default='active')  # 状态：active 或 inactive
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 分类名称
    description = db.Column(db.Text)  # 分类描述
    parent_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)  # 父分类ID
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 商品名称
    sku = db.Column(db.String(50), unique=True, nullable=False)  # 商品编码
    description = db.Column(db.Text)  # 商品描述
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)  # 分类ID
    supplier_id = db.Column(db.Integer, db.ForeignKey('supplier.id'), nullable=True)  # 供应商ID
    unit_price = db.Column(db.Float, nullable=False, default=0.0)  # 单价
    cost_price = db.Column(db.Float, nullable=False, default=0.0)  # 成本价
    unit = db.Column(db.String(20), nullable=False, default='个')  # 单位
    min_stock = db.Column(db.Integer, default=0)  # 最小库存
    max_stock = db.Column(db.Integer, default=1000)  # 最大库存
    image = db.Column(db.String(255))  # 商品图片
    status = db.Column(db.String(20), default='active')  # 状态：active 或 inactive
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Inventory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    current_stock = db.Column(db.Integer, nullable=False, default=0)  # 当前库存
    reserved_stock = db.Column(db.Integer, default=0)  # 预留库存
    available_stock = db.Column(db.Integer, default=0)  # 可用库存
    last_updated = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class StockIn(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)  # 入库数量
    unit_price = db.Column(db.Float, nullable=False)  # 入库单价
    total_amount = db.Column(db.Float, nullable=False)  # 总金额
    batch_number = db.Column(db.String(50))  # 批次号
    operator_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)  # 操作员
    notes = db.Column(db.Text)  # 备注
    status = db.Column(db.String(20), default='completed')  # 状态：pending, completed, cancelled
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class StockOut(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)  # 出库数量
    unit_price = db.Column(db.Float, nullable=False)  # 出库单价
    total_amount = db.Column(db.Float, nullable=False)  # 总金额
    operator_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)  # 操作员
    notes = db.Column(db.Text)  # 备注
    status = db.Column(db.String(20), default='completed')  # 状态：pending, completed, cancelled
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

# 主函数
def main():
    # 检查并删除旧的数据库文件
    if os.path.exists(DB_PATH):
        print(f"正在尝试删除旧的数据库文件: {DB_PATH}")
        try:
            os.remove(DB_PATH)
            print("旧的数据库文件已成功删除")
        except PermissionError:
            print("\n错误：无法删除数据库文件，因为它正在被另一个程序使用。")
            print("解决方法：")
            print("1. 关闭所有可能正在访问该数据库的程序（如PyCharm、Python解释器等）")
            print("2. 手动删除文件: " + DB_PATH)
            print("3. 再次运行此脚本")
            print("\n如果仍然无法删除，请重启电脑后再试。")
            sys.exit(1)
    else:
        print("未找到旧的数据库文件，将创建新的数据库文件")
    
    # 确保instance目录存在
    instance_dir = os.path.dirname(DB_PATH)
    if not os.path.exists(instance_dir):
        print(f"正在创建instance目录: {instance_dir}")
        os.makedirs(instance_dir)
    
    # 在应用上下文中创建数据库表和用户
    with app.app_context():
        # 创建所有数据库表
        print("正在创建数据库表...")
        db.create_all()
        print("数据库表创建成功")
        
        # 创建用户账号
        print("正在创建用户账号...")
        users = [
            # 管理员账号
            User(
                username='admin',
                role='admin',
                name='系统管理员',
                gender='男',
                phone='13800000001',
                email='admin@inventory.com',
                address='北京市朝阳区'
            ),
            # 操作员账号
            User(
                username='operator1',
                role='operator',
                name='张三',
                gender='男',
                phone='13800000002',
                email='zhangsan@inventory.com',
                address='北京市海淀区'
            ),
            User(
                username='operator2',
                role='operator',
                name='李四',
                gender='女',
                phone='13800000003',
                email='lisi@inventory.com',
                address='北京市西城区'
            ),
            User(
                username='operator3',
                role='operator',
                name='王五',
                gender='男',
                phone='13800000004',
                email='wangwu@inventory.com',
                address='北京市东城区'
            )
        ]
        
        # 设置密码
        users[0].set_password('admin123')  # 管理员密码
        for i in range(1, len(users)):
            users[i].set_password('user123')  # 操作员密码
        
        # 添加到会话并提交
        db.session.add_all(users)
        db.session.commit()
        print("用户账号创建成功")
        
        # 创建供应商数据
        print("正在创建供应商数据...")
        suppliers = [
            Supplier(
                name='北京电子科技有限公司',
                contact_person='张经理',
                phone='010-12345678',
                email='zhang@beijing-tech.com',
                address='北京市海淀区中关村大街1号',
                status='active'
            ),
            Supplier(
                name='上海机械制造有限公司',
                contact_person='李经理',
                phone='021-87654321',
                email='li@shanghai-machinery.com',
                address='上海市浦东新区张江高科技园区',
                status='active'
            ),
            Supplier(
                name='广州食品贸易有限公司',
                contact_person='王经理',
                phone='020-11111111',
                email='wang@guangzhou-food.com',
                address='广州市天河区珠江新城',
                status='active'
            ),
            Supplier(
                name='深圳服装批发有限公司',
                contact_person='赵经理',
                phone='0755-22222222',
                email='zhao@shenzhen-fashion.com',
                address='深圳市南山区科技园',
                status='active'
            ),
            Supplier(
                name='杭州文具用品有限公司',
                contact_person='钱经理',
                phone='0571-33333333',
                email='qian@hangzhou-stationery.com',
                address='杭州市西湖区文三路',
                status='active'
            )
        ]
        
        db.session.add_all(suppliers)
        db.session.commit()
        print("供应商数据创建成功")
        
        # 创建商品分类数据
        print("正在创建商品分类数据...")
        categories = [
            # 一级分类
            Category(name='电子产品', description='各类电子设备和配件'),
            Category(name='机械设备', description='工业机械和设备'),
            Category(name='食品饮料', description='各类食品和饮料'),
            Category(name='服装鞋帽', description='服装、鞋类、帽子等'),
            Category(name='办公用品', description='办公文具和用品'),
            # 二级分类
            Category(name='手机', description='智能手机及配件', parent_id=1),
            Category(name='电脑', description='台式机、笔记本及配件', parent_id=1),
            Category(name='家电', description='家用电器', parent_id=1),
            Category(name='工业设备', description='工业机械设备', parent_id=2),
            Category(name='工具', description='各类工具', parent_id=2),
            Category(name='零食', description='各类零食', parent_id=3),
            Category(name='饮料', description='各类饮料', parent_id=3),
            Category(name='男装', description='男士服装', parent_id=4),
            Category(name='女装', description='女士服装', parent_id=4),
            Category(name='文具', description='各类文具用品', parent_id=5),
            Category(name='办公设备', description='办公设备', parent_id=5)
        ]
        
        db.session.add_all(categories)
        db.session.commit()
        print("商品分类数据创建成功")
        
        # 创建商品数据
        print("正在创建商品数据...")
        products = [
            # 电子产品
            Product(
                name='iPhone 15 Pro',
                sku='IPHONE15PRO-128',
                description='苹果iPhone 15 Pro 128GB 深空黑色',
                category_id=6,  # 手机
                supplier_id=1,  # 北京电子科技
                unit_price=7999.0,
                cost_price=6500.0,
                unit='台',
                min_stock=5,
                max_stock=100,
                status='active'
            ),
            Product(
                name='MacBook Pro 14',
                sku='MACBOOKPRO14-M3',
                description='苹果MacBook Pro 14英寸 M3芯片 512GB',
                category_id=7,  # 电脑
                supplier_id=1,
                unit_price=15999.0,
                cost_price=13000.0,
                unit='台',
                min_stock=3,
                max_stock=50,
                status='active'
            ),
            Product(
                name='小米电视 55英寸',
                sku='MI-TV-55',
                description='小米电视 55英寸 4K智能电视',
                category_id=8,  # 家电
                supplier_id=1,
                unit_price=2999.0,
                cost_price=2500.0,
                unit='台',
                min_stock=10,
                max_stock=200,
                status='active'
            ),
            # 机械设备
            Product(
                name='数控机床',
                sku='CNC-MACHINE-001',
                description='高精度数控机床',
                category_id=9,  # 工业设备
                supplier_id=2,  # 上海机械制造
                unit_price=150000.0,
                cost_price=120000.0,
                unit='台',
                min_stock=1,
                max_stock=10,
                status='active'
            ),
            Product(
                name='电动螺丝刀',
                sku='SCREWDRIVER-001',
                description='专业电动螺丝刀套装',
                category_id=10,  # 工具
                supplier_id=2,
                unit_price=299.0,
                cost_price=200.0,
                unit='套',
                min_stock=20,
                max_stock=500,
                status='active'
            ),
            # 食品饮料
            Product(
                name='薯片原味',
                sku='CHIPS-ORIGINAL',
                description='乐事薯片原味 70g',
                category_id=11,  # 零食
                supplier_id=3,  # 广州食品贸易
                unit_price=6.5,
                cost_price=4.0,
                unit='包',
                min_stock=100,
                max_stock=5000,
                status='active'
            ),
            Product(
                name='可口可乐',
                sku='COCA-COLA-330',
                description='可口可乐 330ml 罐装',
                category_id=12,  # 饮料
                supplier_id=3,
                unit_price=3.0,
                cost_price=2.0,
                unit='罐',
                min_stock=200,
                max_stock=10000,
                status='active'
            ),
            # 服装鞋帽
            Product(
                name='男士T恤',
                sku='T-SHIRT-M-BLACK',
                description='纯棉男士T恤 黑色 M码',
                category_id=13,  # 男装
                supplier_id=4,  # 深圳服装批发
                unit_price=89.0,
                cost_price=50.0,
                unit='件',
                min_stock=50,
                max_stock=2000,
                status='active'
            ),
            Product(
                name='女士连衣裙',
                sku='DRESS-F-PINK',
                description='夏季女士连衣裙 粉色 S码',
                category_id=14,  # 女装
                supplier_id=4,
                unit_price=199.0,
                cost_price=120.0,
                unit='件',
                min_stock=30,
                max_stock=1000,
                status='active'
            ),
            # 办公用品
            Product(
                name='中性笔',
                sku='PEN-BLUE-001',
                description='晨光中性笔 0.5mm 蓝色',
                category_id=15,  # 文具
                supplier_id=5,  # 杭州文具用品
                unit_price=2.5,
                cost_price=1.5,
                unit='支',
                min_stock=500,
                max_stock=10000,
                status='active'
            ),
            Product(
                name='A4复印纸',
                sku='PAPER-A4-500',
                description='得力A4复印纸 70g 500张/包',
                category_id=16,  # 办公设备
                supplier_id=5,
                unit_price=25.0,
                cost_price=18.0,
                unit='包',
                min_stock=100,
                max_stock=2000,
                status='active'
            )
        ]
        
        db.session.add_all(products)
        db.session.commit()
        print("商品数据创建成功")
        
        # 创建库存记录
        print("正在创建库存记录...")
        inventory_records = []
        for product in products:
            # 随机生成初始库存
            initial_stock = random.randint(50, 500)
            reserved_stock = random.randint(0, 20)
            available_stock = initial_stock - reserved_stock
            
            inventory_records.append(Inventory(
                product_id=product.id,
                current_stock=initial_stock,
                reserved_stock=reserved_stock,
                available_stock=available_stock
            ))
        
        db.session.add_all(inventory_records)
        db.session.commit()
        print("库存记录创建成功")
        
        # 创建入库记录
        print("正在创建入库记录...")
        stock_in_records = []
        operator_ids = [user.id for user in users if user.role == 'operator']
        
        for product in products:
            # 为每个商品创建3-5条入库记录
            num_records = random.randint(3, 5)
            for i in range(num_records):
                quantity = random.randint(10, 100)
                unit_price = product.cost_price * random.uniform(0.9, 1.1)  # 成本价上下浮动10%
                total_amount = quantity * unit_price
                
                # 生成批次号
                batch_number = f"IN{product.id:03d}{random.randint(1000, 9999)}"
                
                
                stock_in_records.append(StockIn(
                    product_id=product.id,
                    quantity=quantity,
                    unit_price=round(unit_price, 2),
                    total_amount=round(total_amount, 2),
                    batch_number=batch_number,
                    operator_id=random.choice(operator_ids),
                    notes=f"批量入库 - {product.name}",
                    status='completed'
                ))
        
        db.session.add_all(stock_in_records)
        db.session.commit()
        print("入库记录创建成功")
        
        # 创建出库记录
        print("正在创建出库记录...")
        stock_out_records = []
        
        for product in products:
            # 为每个商品创建2-4条出库记录
            num_records = random.randint(2, 4)
            for i in range(num_records):
                quantity = random.randint(5, 50)
                unit_price = product.unit_price * random.uniform(0.95, 1.05)  # 售价上下浮动5%
                total_amount = quantity * unit_price
                
                stock_out_records.append(StockOut(
                    product_id=product.id,
                    quantity=quantity,
                    unit_price=round(unit_price, 2),
                    total_amount=round(total_amount, 2),
                    operator_id=random.choice(operator_ids),
                    notes=f"客户订单 - {product.name}",
                    status='completed'
                ))
        
        db.session.add_all(stock_out_records)
        db.session.commit()
        print("出库记录创建成功")
        
        print("\n=== 库存管理系统初始化数据创建完成 ===")
        print("\n测试账号信息：")
        print("管理员账号: username=admin, password=admin123")
        print("操作员账号: username=operator1, password=user123")
        print("操作员账号: username=operator2, password=user123")
        print("操作员账号: username=operator3, password=user123")
        print("\n数据统计：")
        print(f"- 用户数量: {len(users)}")
        print(f"- 供应商数量: {len(suppliers)}")
        print(f"- 商品分类数量: {len(categories)}")
        print(f"- 商品数量: {len(products)}")
        print(f"- 库存记录: {len(inventory_records)}")
        print(f"- 入库记录: {len(stock_in_records)}")
        print(f"- 出库记录: {len(stock_out_records)}")
        print("\n示例商品信息：")
        for product in products[:5]:  # 显示前5个商品
            print(f"- {product.name} (SKU: {product.sku}) - ¥{product.unit_price}")
        print("\n请注意：首次登录后请修改密码以保证账号安全。")

if __name__ == '__main__':
    main()
